"""
Case Type   : explain
Case Name   : explain select * from 视图(视图含有<<<操作符)
Create At   : 2024/7/12
Owner       : songjing20
Description :
    1.创建表
    2.创建函数
    3.创建运算符
    4.创建视图
    5.查询表
    6.explain查询执行计划
    7.清理环境
Expect      :
    1.建表成功
    2.成功
    3.成功
    4.成功
    5.4条数据
    6.查询成功，seq scan
    7.成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.table = 't_explain_0072'
        self.view = 'v_explain_0072'
        self.func = 'f_explain_0072'
        self.constant = Constant()

    def test_explain(self):
        step = '-----step1:创建表;expect:建表成功-----'
        self.log.info(step)
        create_table = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.table} cascade;"
            f"create table {self.table} as select x as a, 10001-x as b "
            f"from generate_series(1, 10000) x;")
        self.log.info(create_table)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table,
                      f"建表失败:{step}")

        step = '-----step2:创建函数;expect:成功-----'
        self.log.info(step)
        create_func = self.pri_sh.execut_db_sql(
            f'''create function {self.func}(integer,integer) returns boolean 
            as \\$\\$
            begin
              return \\$1 < \\$2;
            end
            \\$\\$ language plpgsql immutable;''')
        self.log.info(create_func)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, create_func,
                      f"创建函数失败:{step}")

        step = '-----step3:创建运算符;expect:成功-----'
        self.log.info(step)
        res = self.pri_sh.execut_db_sql(
            f"create operator <<< (procedure=f_explain_0072, leftarg=integer, "
            f"rightarg=integer, restrict=scalarltsel);")
        self.log.info(res)
        self.assertIn("CREATE OPERATOR", res, f"执行失败:{step}")

        step = '-----step4:创建视图;expect:成功-----'
        self.log.info(step)
        create_view_res = self.pri_sh.execut_db_sql(
            f"drop view if exists {self.view} cascade;"
            f"create view {self.view} "
            f"as select * from {self.table} where b <<< 5;")
        self.log.info(create_view_res)
        self.assertIn(self.constant.CREATE_VIEW_SUCCESS_MSG, create_view_res,
                      f"执行失败:{step}")

        step = '-----step5:查询视图数据;expect:4条数据-----'
        self.log.info(step)
        res = self.pri_sh.execut_db_sql(
            f"select * from {self.view};")
        self.log.info(res)
        self.assertIn("9997 | 4\n  9998 | 3\n  9999 | 2\n 10000 | 1\n"
                      "(4 rows)", res, f"执行失败:{step}")

        step = '-----step6:explain查询执行计划;expect:查询成功，seq scan-----'
        self.log.info(step)
        explain_res = self.pri_sh.execut_db_sql(
            f"explain select * from {self.view};")
        self.log.info(explain_res)
        self.assertIn(f"Seq Scan on {self.table}", explain_res,
                      f"执行失败:{step}")
        self.assertIn("Filter: (b <<< 5)", explain_res, f"执行失败:{step}")

    def tearDown(self):
        step = '-----step7:清理环境 expect:成功-----'
        self.log.info(step)
        drop_res = self.pri_sh.execut_db_sql(
            f"drop table {self.table} cascade;"
            f"drop function {self.func}() cascade")
        self.log.info(drop_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      f"执行失败:{step}")
        self.assertIn('NOTICE:  drop cascades to operator <<<(integer,'
                      'integer)', drop_res, f"执行失败:{step}")
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, drop_res,
                      f"执行失败:{step}")
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
